[新機能]dbtでSQL上の1つ1つのロジックに対しテストを行える「Unit tests」を試してみた

モダンデータスタック(MDS)

#dbt

#dbt Cloud

さがら

2024.05.25

さがらです。

2024年5月9日に、dbt-core ver1.8がリリースされました。

このver1.8の新機能として、dbtで開発したSQLのModelに単体テストを行える「Unit tests」が追加されました。

このUnit testsを試してみたので、本記事で内容をまとめてみます。

Unit testsとは

まず、Unit testsについて簡単に説明します。

日本語訳すると、「単体テスト」に該当するものとなります。ソフトウェア開発においては関数・メソッドなどのレベルで単体テストを実装することが多いと思います。

dbtでもこれまでテストの機能はあったのですが、dbtでの変換前のデータやdbtでの変換後のデータという「データ自体」に対するテストのみが可能であり、SQL上の1つ1つのロジックに対して挙動を保証するためのテストはdbt-coreだけではできませんでした。（dbt-unit-testingなどの外部packageを使う必要がありました。）

そんな状況の中、今回のdbt-core ver1.8にて、SQL上の1つ1つのロジックに対してテストを行える機能が「Unit tests」として追加されたのです。待ち望んでいた方も多いと思います！

やること

今回、dbt Labs社のGitHubアカウントで公開されている「jaffle-shop」のリポジトリをforkし、定義済のUnit testsがどのように動くのかを確かめてみます。（私は9a147c5のコミットを用いて検証しました。）

forkしてdbt Cloudでdbt projectのセットアップを終えた後、dbt_project.ymlのseed-pathsを下記の内容に書き換えます。

seed-paths: ["seeds", "jaffle-data"]

その後、dbt seedとdbt run --emptyを実行しておきます。これでUnit testsの動作検証を行うための事前準備はOKです。（参考までに、--emptyもver1.8で追加されたフラグで、ModelのrefとsourceをコンパイルしたFROM句の後ろにwhere false limit 0を追加して実行することで、0行のテーブル・ビューを生成することが出来るフラグとなっています。）

Unit testsの定義方法と実行方法の確認

このリポジトリでは、stg_locationsとordersという2つのModelに対してUnit testsが定義されています。ここではstg_locationsを例に、どのようにUnit testsを定義して実行するのかを確認してみます。

対象のModelの確認

まず、Unit testsの対象となっているModelstg_locations.sqlの内容が下記となります。内容としては、下記の変換処理が行われています。

Sourceであるraw_storesテーブルの各カラムをリネーム
opened_atカラムについては日付部分のみを抽出

with

source as (

    select * from {{ source('ecom', 'raw_stores') }}

),

renamed as (

    select

        ----------  ids
        id as location_id,

        ---------- text
        name as location_name,

        ---------- numerics
        tax_rate,

        ---------- timestamps
        {{ dbt.date_trunc('day', 'opened_at') }} as opened_date

    from source

)

select * from renamed

Unit testsの定義

この上で、Unit testsを定義しているstg_locations.ymlの内容が下記となります。各項目の説明も併せて記載しておきます。

name:で、Unit testsの名前を指定
description:で、Unit testsでどんなテストを行っているかを説明
given:で、Unit testsに使用するデータを定義
- input:にsource('ecom', 'raw_stores')と入れることで、Unit tests実行時にはこのsourceの代わりに後続のrowsで定義したモックデータを入れてModelを実行する
- rows:で、実際に入れるデータを指定。ここではdictの形式で指定している
expect:で、given:で指定したモックデータを使ってModel実行後に期待されるデータを定義。ここで定義した「期待されるデータ」と「Model実行後のデータ」が一致しなければ、Unit testsは失敗となる
- rowsで、モックデータを使ってModel実行後に期待されるデータを定義。ここではdictの形式で指定している

models:
  - name: stg_locations
    description: List of open locations with basic cleaning and transformation applied, one row per location.
    columns:
      - name: location_id
        description: The unique key for each location.
        data_tests:
          - not_null
          - unique

unit_tests:
  - name: test_does_location_opened_at_trunc_to_date
    description: "Check that opened_at timestamp is properly truncated to a date."
    model: stg_locations
    given:
      - input: source('ecom', 'raw_stores')
        rows:
          - {
              id: 1,
              name: "Vice City",
              tax_rate: 0.2,
              opened_at: "2016-09-01T00:00:00",
            }
          - {
              id: 2,
              name: "San Andreas",
              tax_rate: 0.1,
              opened_at: "2079-10-27T23:59:59.9999",
            }
    expect:
      rows:
        - {
            location_id: 1,
            location_name: "Vice City",
            tax_rate: 0.2,
            opened_date: "2016-09-01",
          }
        - {
            location_id: 2,
            location_name: "San Andreas",
            tax_rate: 0.1,
            opened_date: "2079-10-27",
          }

Unit testsの実行

では、実際にUnit testsを実行してみます。

Unit testsだけを実行する場合にはdbt test --select test_does_location_opened_at_trunc_to_dateのように、対象のUnit tests名を--selectで指定してあげればOKです。

実際に実行してみると、下記の結果が得られました。Detailsのログを見ると、実際にSourceの内容をモックデータに書き換えてModelが実行されたデータとexpect:で指定したデータをUnionして比較して、テスト結果を出力ということを行っています。

Detailsのログ　※クリックで展開

13:54:37 Began running node unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date
13:54:37 1 of 1 START unit_test stg_locations::test_does_location_opened_at_trunc_to_date  [RUN]
13:54:37 Began compiling node unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date
13:54:37 Began executing node unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date
13:54:37 Using snowflake connection "unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date"
13:54:37 On unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date: /* {"app": "dbt", "dbt_version": "2024.5.164", "profile_name": "user", "target_name": "default", "node_id": "unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date"} */
describe table SAGARA_JAFFLE_SHOP.raw.raw_stores
13:54:37 Opening a new connection, currently in state closed
13:54:38 SQL status: SUCCESS 4 in 1.0 seconds
13:54:38 Writing injected SQL for node "unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date"
13:54:38 Writing injected SQL for node "unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date"
13:54:38 Using snowflake connection "unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date"
13:54:38 On unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date: /* {"app": "dbt", "dbt_version": "2024.5.164", "profile_name": "user", "target_name": "default", "node_id": "unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date"} */
create or replace temporary table SAGARA_JAFFLE_SHOP.dbt_ssagara.test_does_location_opened_at_trunc_to_date__dbt_tmp
         as
        (select * from (
        with

 __dbt__cte__raw_stores as (

-- Fixture for raw_stores
select 
    
        try_cast('1' as character varying(16777216))
     as id, 
    
        try_cast('Vice City' as character varying(16777216))
     as name, 
    
        try_cast('2016-09-01T00:00:00' as TIMESTAMP_NTZ)
     as opened_at, 
    
        try_cast('0.2' as FLOAT)
     as tax_rate
union all
select 
    
        try_cast('2' as character varying(16777216))
     as id, 
    
        try_cast('San Andreas' as character varying(16777216))
     as name, 
    
        try_cast('2079-10-27T23:59:59.9999' as TIMESTAMP_NTZ)
     as opened_at, 
    
        try_cast('0.1' as FLOAT)
     as tax_rate
), source as (

    select * from __dbt__cte__raw_stores

),

renamed as (

    select

        ----------  ids
        id as location_id,

        ---------- text
        name as location_name,

        ---------- numerics
        tax_rate,

        ---------- timestamps
        date_trunc('day', opened_at) as opened_date

    from source

)

select * from renamed
    ) as __dbt_sbq
    where false
    limit 0

        );
13:54:39 SQL status: SUCCESS 1 in 1.0 seconds
13:54:39 Using snowflake connection "unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date"
13:54:39 On unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date: /* {"app": "dbt", "dbt_version": "2024.5.164", "profile_name": "user", "target_name": "default", "node_id": "unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date"} */
-- Build actual result given inputs
with dbt_internal_unit_test_actual as (
  select
    location_id,location_name,tax_rate,opened_date, 'actual' as "actual_or_expected"
  from (
    with

 __dbt__cte__raw_stores as (

-- Fixture for raw_stores
select 
    
        try_cast('1' as character varying(16777216))
     as id, 
    
        try_cast('Vice City' as character varying(16777216))
     as name, 
    
        try_cast('2016-09-01T00:00:00' as TIMESTAMP_NTZ)
     as opened_at, 
    
        try_cast('0.2' as FLOAT)
     as tax_rate
union all
select 
    
        try_cast('2' as character varying(16777216))
     as id, 
    
        try_cast('San Andreas' as character varying(16777216))
     as name, 
    
        try_cast('2079-10-27T23:59:59.9999' as TIMESTAMP_NTZ)
     as opened_at, 
    
        try_cast('0.1' as FLOAT)
     as tax_rate
), source as (

    select * from __dbt__cte__raw_stores

),

renamed as (

    select

        ----------  ids
        id as location_id,

        ---------- text
        name as location_name,

        ---------- numerics
        tax_rate,

        ---------- timestamps
        date_trunc('day', opened_at) as opened_date

    from source

)

select * from renamed
  ) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected as (
  select
    location_id, location_name, tax_rate, opened_date, 'expected' as "actual_or_expected"
  from (
    select 
    
        try_cast('1' as character varying(16777216))
     as location_id, 
    
        try_cast('Vice City' as character varying(16777216))
     as location_name, 
    
        try_cast('0.2' as FLOAT)
     as tax_rate, 
    
        try_cast('2016-09-01' as TIMESTAMP_NTZ)
     as opened_date
union all
select 
    
        try_cast('2' as character varying(16777216))
     as location_id, 
    
        try_cast('San Andreas' as character varying(16777216))
     as location_name, 
    
        try_cast('0.1' as FLOAT)
     as tax_rate, 
    
        try_cast('2079-10-27' as TIMESTAMP_NTZ)
     as opened_date
  ) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected
13:54:40 SQL status: SUCCESS 4 in 0.0 seconds
13:54:40 Applying DROP to: SAGARA_JAFFLE_SHOP.dbt_ssagara.test_does_location_opened_at_trunc_to_date__dbt_tmp
13:54:40 Using snowflake connection "unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date"
13:54:40 On unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date: /* {"app": "dbt", "dbt_version": "2024.5.164", "profile_name": "user", "target_name": "default", "node_id": "unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date"} */
drop table if exists SAGARA_JAFFLE_SHOP.dbt_ssagara.test_does_location_opened_at_trunc_to_date__dbt_tmp cascade
13:54:40 SQL status: SUCCESS 1 in 0.0 seconds
13:54:40 On unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date: Close
13:54:40 1 of 1 PASS stg_locations::test_does_location_opened_at_trunc_to_date .......... [PASS in 3.30s]
13:54:40 Finished running node unit_test.jaffle_shop.stg_locations.test_does_location_opened_at_trunc_to_date

また、dbt buildを行ったときはどのような順番で実行されるのかも確認してみます。dbt build --select stg_locationsを実行してみると、下記の順番で実行されていることがわかります。

Unit testsを実行
Modelを実行
Data tests(not nullやuniqueなど)を実行

Modelの実行前にUnit testsを行ってくれるので、Unit testsが失敗した場合にはModelが実行されないようになっています。下図は、rowsのデータをUnit testsが失敗するように変更した上で、dbt build --select stg_locationsを実行したときの結果です。

複数のモックデータが必要な場合の定義方法

もう一つ例として、複数のモックデータが必要な場合のUnit testsの定義方法についても簡単にまとめておきます。

対象のModelの確認

複数のモックデータを使用したUnit testsの対象となっているModelorders.sqlの内容が下記となります。ref('stg_orders')とref('order_items')という形で、2つのModelを参照していることがわかります。

with

orders as (

    select * from {{ ref('stg_orders') }}

),

order_items as (

    select * from {{ ref('order_items') }}

),

order_items_summary as (

    select
        order_id,

        sum(supply_cost) as order_cost,
        sum(product_price) as order_items_subtotal,
        count(order_item_id) as count_order_items,
        sum(
            case
                when is_food_item then 1
                else 0
            end
        ) as count_food_items,
        sum(
            case
                when is_drink_item then 1
                else 0
            end
        ) as count_drink_items

    from order_items

    group by 1

),

compute_booleans as (

    select
        orders.*,

        order_items_summary.order_cost,
        order_items_summary.order_items_subtotal,
        order_items_summary.count_food_items,
        order_items_summary.count_drink_items,
        order_items_summary.count_order_items,
        order_items_summary.count_food_items > 0 as is_food_order,
        order_items_summary.count_drink_items > 0 as is_drink_order

    from orders

    left join
        order_items_summary
        on orders.order_id = order_items_summary.order_id

),

customer_order_count as (

    select
        *,

        row_number() over (
            partition by customer_id
            order by ordered_at asc
        ) as customer_order_number

    from compute_booleans

)

select * from customer_order_count

Unit testsの定義

この上で、Unit testsの定義を行っているorders.ymlを見ると、下記のようになっています。given:の中でinput: ref('order_items')とinput: ref('stg_orders')のように、2回inputを定義してあげれば、複数のモックデータが必要な場合でも問題ありません。

models:
  - name: orders

# （中略）

unit_tests:
  - name: test_order_items_compute_to_bools_correctly
    description: "Test that the counts of drinks and food orders convert to booleans properly."
    model: orders
    given:
      - input: ref('order_items')
        rows:
          - {
              order_id: 1,
              order_item_id: 1,
              is_drink_item: false,
              is_food_item: true,
            }
          - {
              order_id: 1,
              order_item_id: 2,
              is_drink_item: true,
              is_food_item: false,
            }
          - {
              order_id: 2,
              order_item_id: 3,
              is_drink_item: false,
              is_food_item: true,
            }
      - input: ref('stg_orders')
        rows:
          - { order_id: 1 }
          - { order_id: 2 }
    expect:
      rows:
        - {
            order_id: 1,
            count_food_items: 1,
            count_drink_items: 1,
            is_drink_order: true,
            is_food_order: true,
          }
        - {
            order_id: 2,
            count_food_items: 1,
            count_drink_items: 0,
            is_drink_order: false,
            is_food_order: true,
          }

# （中略）

モックデータをCSVとして定義する場合の定義方法

今回検証に用いているリポジトリでは行っていませんが、モックデータをyaml上でdict形式で定義するのではなく、CSVとして定義することも可能です。

実際に、stg_locationsを例にやってみます。

yaml上でCSVを直接記述する方法

まずはシンプルな方法として、CSVの形式でyaml上に記述することも可能です。

具体例として、CSVを直接記述する場合stg_locations.ymlの内容を下記のようになります。

models:
  - name: stg_locations
    description: List of open locations with basic cleaning and transformation applied, one row per location.
    columns:
      - name: location_id
        description: The unique key for each location.
        data_tests:
          - not_null
          - unique

unit_tests:
  - name: test_does_location_opened_at_trunc_to_date
    description: "Check that opened_at timestamp is properly truncated to a date."
    model: stg_locations
    given:
      - input: source('ecom', 'raw_stores')
        format: csv
        rows: |
          id,name,tax_rate,opened_at
          "1","Vice City","0.2","2016-09-01T00:00:00"
          "2","San Andreas","0.1","2079-10-27T23:59:59.9999"
    expect:
      format: csv
      rows: |
        location_id,location_name,tax_rate,opened_date
        "1","Vice City","0.2","2016-09-01"
        "2","San Andreas","0.1","2079-10-27"

別途CSVファイルを定義する方法

また、対象のCSVファイルを別フォルダに置いて管理することも可能です。

具体的には、下記のようにすればOKです。

dbt_project.ymlのtest-paths:で指定されているフォルダの配下にfixturesフォルダを追加し、その中でCSVファイルを定義
Unit testsを定義するyamlからCSVを参照する際は、.csvの拡張子を抜いたファイル名をfixture: stg_locations_givenとして参照する

以下、各ファイルの記述例を記載します。

dbt_project.yml

config-version: 2

# （中略）

test-paths: ["data-tests"]

# （中略）

data-tests/fixtures/stg_locations_given.csv

id,name,tax_rate,opened_at
"1","Vice City","0.2","2016-09-01T00:00:00"
"2","San Andreas","0.1","2079-10-27T23:59:59.9999"

data-tests/fixtures/stg_locations_expect.csv

location_id,location_name,tax_rate,opened_date
"1","Vice City","0.2","2016-09-01"
"2","San Andreas","0.1","2079-10-27"

models/staging/stg_locations.yml

models:
  - name: stg_locations

# （中略）

unit_tests:
  - name: test_does_location_opened_at_trunc_to_date
    description: "Check that opened_at timestamp is properly truncated to a date."
    model: stg_locations
    given:
      - input: source('ecom', 'raw_stores')
        format: csv
        fixture: stg_locations_given
    expect:
      format: csv
      fixture: stg_locations_expect